package com.bojx.report.service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.bojx.report.dao.QuarterReportDao;
import com.bojx.report.dao.QueryDao;
import com.bojx.report.pojo.Constant;
import com.jfinal.kit.Kv;
import com.jfinal.plugin.activerecord.Record;

public class QuarterValidation {
	private static String[] banks = new String[] { "人民银行", "国开银行", "农发银行", "进出口银行", "工商银行", "农业银行", "中国银行", "建设银行",
			"邮储银行", "交通银行", "中信银行", "光大银行", "华夏银行", "广发银行", "平安银行", "招商银行", "浦发银行", "兴业银行", "民生银行", "恒丰银行", "浙商银行",
			"渤海银行", "城商行", "农信系统", "外资银行", "村镇银行", "财务公司" };
	public static String[] areas = new String[] { "本级", "嘉善", "平湖", "桐乡", "海宁", "海盐" };
	public static QuarterReportDao quarterReportDao = new QuarterReportDao();
	private static QueryDao queryDao = new QueryDao();

	public void validate(Workbook wb, String bank, int year, int quarter) throws Exception {
		//校验sheet存不存在
		int sheetNums=wb.getNumberOfSheets();
		if(sheetNums!=7){
			throw new Exception("文件格式不对,请按模板填，不要删除任何工作表");
		}
		
		/**
		 * 校验从第9行到第35行是不是符合要求
		 */
		for (int i = 0; i < 6; i++) {
			Sheet sheet = wb.getSheetAt(i);
			for (int j = 0; j < 27; j++) {
				Row row = sheet.getRow(j + 8);
				Cell cell = row.getCell(0);
				String content = cell.getStringCellValue();
				if (!banks[j].equals(content.trim())) {
					throw new Exception("文件格式不对,请按模板填，不要删除任何一行");
				}
			}
		}
		int month = 0;
		switch (quarter) {
		case 1:
			month = 3;
			break;
		case 2:
			month = 6;
			break;
		case 3:
			month = 9;
			break;
		case 4:
			month = 12;
			break;
		}
		//验证月报有没有传
		Record monthReport1=queryDao.findUploadExcel(bank,year,month);
		if(monthReport1==null){
			throw new Exception(month+"月月报还未上传！");
		}
		Record monthReport2=queryDao.findUploadExcel(bank,year,month-1);
		if(monthReport2==null){
			throw new Exception((month-1)+"月月报还未上传！");
		}
		Record monthReport3=queryDao.findUploadExcel(bank,year,month-2);
		if(monthReport3==null){
			throw new Exception((month-2)+"月月报还未上传！");
		}
		List<String> errorTips = new ArrayList<String>();
		// 校验银行网点情况和客户情况
		for (String area : areas) {
			Sheet sheet = wb.getSheet(area);
			int rowNum = quarterReportDao.queryRowNum(bank);
			Row row = sheet.getRow(rowNum - 1);
			// 查询网点情况
			Record clientInfo = queryDao.queryClientInfo(bank, area, year, month);
			if (clientInfo != null) {
				int euserQtyMonth = clientInfo.getInt("euser_qty");// 全部企业类客户数量
				int ebankEuserQtyMonth = clientInfo.getInt("ebank_euser_qty");// 开通网银的企业类客户数量
				int draftEuserQtyMonth = clientInfo.getInt("draft_euser_qty");// 已使用商业汇票客户数量
				int edraftEuserQtyMonth = clientInfo.getInt("edraft_euser_qty");// 已开通电子商业汇票业务客户数量

				int euserQtyQuarter = (int) row.getCell(3).getNumericCellValue();
				int ebankEuserQtyQuarter = (int) row.getCell(4).getNumericCellValue();
				int draftEuserQtyQuarter = (int) row.getCell(5).getNumericCellValue();
				int edraftEuserQtyQuarter = (int) row.getCell(6).getNumericCellValue();
				if (euserQtyMonth != euserQtyQuarter) {
					errorTips.add(new String(area + "全部企业客户数量与" + month + "月月报不一致"));
				}
				if (ebankEuserQtyMonth != ebankEuserQtyQuarter) {
					errorTips.add(new String(area + "开通网银的企业类客户数量与" + month + "月月报不一致"));
				}
				if (draftEuserQtyMonth != draftEuserQtyQuarter) {
					errorTips.add(new String(area + "已使用商业汇票客户数量与" + month + "月月报不一致"));
				}
				if (edraftEuserQtyMonth != edraftEuserQtyQuarter) {
					errorTips.add(new String(area + "已开通电子商业汇票业务客户数量与" + month + "月月报不一致"));
				}
			}

			// 网点情况
			Record branchInfo = queryDao.queryBranchInfo(bank, area, year, month);
			if (branchInfo != null) {
				int branchQtyMonth = branchInfo.getInt("branch_qty");// 对公对网点数量
				int edraftBranchQtyMonth = branchInfo.getInt("edraft_branch_qty");// 已加入电子商业汇票系统网点数量
				int branchQtyQuarter = (int) row.getCell(1).getNumericCellValue();
				int edraftBranchQtyQuarter = (int) row.getCell(2).getNumericCellValue();
				if (branchQtyMonth != branchQtyQuarter) {
					errorTips.add(new String(area + "对公对网点数量与" + month + "月月报不一致"));
				}
				if (edraftBranchQtyMonth != edraftBranchQtyQuarter) {
					errorTips.add(new String(area + "已加入电子商业汇票系统网点数量与" + month + "月月报不一致"));
				}
			}

			String ret1 = validateMeanSection(row, area, 7, 8, Constant.less50, "电子银行承兑汇票承兑");
			if (ret1 != null) {
				errorTips.add(ret1);
			}

			String ret2 = validateMeanSection(row, area, 9, 10, Constant.bet50To100, "电子银行承兑汇票承兑");
			if (ret2 != null) {
				errorTips.add(ret2);
			}

			String ret3 = validateMeanSection(row, area, 11, 12, Constant.bet100To300, "电子银行承兑汇票承兑");
			if (ret3 != null) {
				errorTips.add(ret3);
			}

			String ret4 = validateMeanSection(row, area, 13, 14, Constant.gt300, "电子银行承兑汇票承兑");
			if (ret4 != null) {
				errorTips.add(ret4);
			}

			String ret5 = validateMeanSection(row, area, 15, 16, Constant.less50, "电子银行承兑汇票贴现");
			if (ret5 != null) {
				errorTips.add(ret5);
			}

			String ret6 = validateMeanSection(row, area, 17, 18, Constant.bet50To100, "电子银行承兑汇票贴现");
			if (ret6 != null) {
				errorTips.add(ret6);
			}

			String ret7 = validateMeanSection(row, area, 19, 20, Constant.bet100To300, "电子银行承兑汇票贴现");
			if (ret7 != null) {
				errorTips.add(ret7);
			}

			String ret8 = validateMeanSection(row, area, 21, 22, Constant.gt300, "电子银行承兑汇票贴现");
			if (ret8 != null) {
				errorTips.add(ret8);
			}

			// 纸质银行承兑汇票承兑
			String ret9 = validateMeanSection(row, area, 23, 24, Constant.less50, "纸质银行承兑汇票承兑");
			if (ret9 != null) {
				errorTips.add(ret9);
			}

			String ret10 = validateMeanSection(row, area, 25, 26, Constant.bet50To100, "纸质银行承兑汇票承兑");
			if (ret10 != null) {
				errorTips.add(ret10);
			}

			String ret11 = validateMeanSection(row, area, 27, 28, Constant.bet100To300, "纸质银行承兑汇票承兑");
			if (ret11 != null) {
				errorTips.add(ret11);
			}

			String ret12 = validateMeanSection(row, area, 29, 30, Constant.gt300, "纸质银行承兑汇票承兑");
			if (ret12 != null) {
				errorTips.add(ret12);
			}
			// 纸质银行承兑汇票贴现
			String ret13 = validateMeanSection(row, area, 31, 32, Constant.less50, "纸质银行承兑汇票贴现");
			if (ret13 != null) {
				errorTips.add(ret13);
			}

			String ret14 = validateMeanSection(row, area, 33, 34, Constant.bet50To100, "纸质银行承兑汇票贴现");
			if (ret13 != null) {
				errorTips.add(ret14);
			}

			String ret15 = validateMeanSection(row, area, 35, 36, Constant.bet100To300, "纸质银行承兑汇票贴现");
			if (ret15 != null) {
				errorTips.add(ret15);
			}

			String ret16 = validateMeanSection(row, area, 37, 38, Constant.gt300, "纸质银行承兑汇票贴现");
			if (ret16 != null) {
				errorTips.add(ret16);
			}

			// 电子商业承兑汇票贴现
			String ret17 = validateMeanSection(row, area, 39, 40, Constant.less50, "电子商业承兑汇票贴现");
			if (ret17 != null) {
				errorTips.add(ret17);
			}

			String ret18 = validateMeanSection(row, area, 41, 42, Constant.bet50To100, "电子商业承兑汇票贴现");
			if (ret18 != null) {
				errorTips.add(ret18);
			}

			String ret19 = validateMeanSection(row, area, 43, 44, Constant.bet100To300, "电子商业承兑汇票贴现");
			if (ret19 != null) {
				errorTips.add(ret19);
			}

			String ret20 = validateMeanSection(row, area, 45, 46, Constant.gt300, "电子商业承兑汇票贴现");
			if (ret20 != null) {
				errorTips.add(ret20);
			}

			// 纸质商业承兑汇票贴现
			String ret21 = validateMeanSection(row, area, 47, 48, Constant.less50, "纸质商业承兑汇票贴现");
			if (ret21 != null) {
				errorTips.add(ret21);
			}

			String ret22 = validateMeanSection(row, area, 49, 50, Constant.bet50To100, "纸质商业承兑汇票贴现");
			if (ret22 != null) {
				errorTips.add(ret22);
			}

			String ret23 = validateMeanSection(row, area, 51, 52, Constant.bet100To300, "纸质商业承兑汇票贴现");
			if (ret23 != null) {
				errorTips.add(ret23);
			}

			String ret24 = validateMeanSection(row, area, 53, 54, Constant.gt300, "纸质商业承兑汇票贴现");
			if (ret24 != null) {
				errorTips.add(ret24);
			}

			// 商业汇票再贴现

			String ret25 = validateMeanSection(row, area, 55, 56, Constant.less50, "商业汇票再贴现");
			if (ret25 != null) {
				errorTips.add(ret25);
			}

			String ret26 = validateMeanSection(row, area, 57, 58, Constant.bet50To100, "商业汇票再贴现");
			if (ret26 != null) {
				errorTips.add(ret26);
			}

			String ret27 = validateMeanSection(row, area, 59, 60, Constant.bet100To300, "商业汇票再贴现");
			if (ret27 != null) {
				errorTips.add(ret27);
			}

			String ret28 = validateMeanSection(row, area, 61, 62, Constant.gt300, "商业汇票再贴现");
			if (ret28 != null) {
				errorTips.add(ret28);
			}

			// 电子银行承兑汇票的承兑量
			int baEDraftAcceptQtyQuarter = calculateQty(row, new int[] { 7, 9, 11, 13 });
			double baEDraftAcceptAmtQuarter = calculateAmt(row, new int[] { 8, 10, 12, 14 });
			Kv con1=Kv.by("area", area).set("organization", bank).set("year", year)
					.set("quarter", quarter).set("billType", Constant.BAEDRAFT)
					.set("actionType", Constant.ACCEPT).set("statisticsType", Constant.CURRENT);
            Record acceptRecord=queryDao.queryBusiness(con1);
            int baEDraftAcceptQtyMonth=acceptRecord.getInt("qty");
            double baEDraftAcceptAmtMonth=acceptRecord.getDouble("amt");
            if(baEDraftAcceptQtyQuarter!=baEDraftAcceptQtyMonth){
            	StringBuffer sb=new StringBuffer();
            	sb.append(area);
            	sb.append("电子银行承兑汇票的承兑笔数与每月汇总不同");
            	errorTips.add(sb.toString());
            }
            if(baEDraftAcceptAmtQuarter!=baEDraftAcceptAmtMonth){
            	StringBuffer sb=new StringBuffer();
            	sb.append(area);
            	sb.append("电子银行承兑汇票的承兑金额与每月汇总不同");
            	errorTips.add(sb.toString());
            }
			// 电子银行承兑汇票的贴现量
			int baEDraftDiscountQtyQuarter = calculateQty(row, new int[] { 15, 17, 19, 21 });
			double baEDraftDiscountAmtQuarter = calculateAmt(row, new int[] { 16, 18, 20,22 });
			Kv con2=Kv.by("area", area).set("organization", bank).set("year", year)
					.set("quarter", quarter).set("billType", Constant.BAEDRAFT)
					.set("actionType", Constant.DISCOUNT).set("statisticsType", Constant.CURRENT);
            Record discountRecord=queryDao.queryBusiness(con2);
            int baEDraftDiscountQtyMonth=discountRecord.getInt("qty");
            double baEDraftDiscountAmtMonth=discountRecord.getDouble("amt");
            if(baEDraftDiscountQtyQuarter!=baEDraftDiscountQtyMonth){
            	StringBuffer sb=new StringBuffer();
            	sb.append(area);
            	sb.append("电子银行承兑汇票的贴现笔数与每月汇总不同");
            	errorTips.add(sb.toString());
            }
            if(baEDraftDiscountAmtQuarter!=baEDraftDiscountAmtMonth){
            	StringBuffer sb=new StringBuffer();
            	sb.append(area);
            	sb.append("电子银行承兑汇票的贴现金额与每月汇总不同");
            	errorTips.add(sb.toString());
            }
			
			// 电子商业承兑汇票的贴现量
			int caEDraftDiscountQtyQuarter = calculateQty(row, new int[] { 39, 41, 43, 45 });
			double caEDraftDiscountAmtQuarter = calculateAmt(row, new int[] { 40, 42, 44, 46 });
			Kv con3=Kv.by("area", area).set("organization", bank).set("year", year)
					.set("quarter", quarter).set("billType", Constant.CAEDRAFT)
					.set("actionType", Constant.DISCOUNT).set("statisticsType", Constant.CURRENT);
            Record caDiscountRecord=queryDao.queryBusiness(con3);
            int caEDraftDiscountQtyMonth=caDiscountRecord.getInt("qty");
            double caEDraftDiscountAmtMonth=caDiscountRecord.getDouble("amt");
            if(caEDraftDiscountQtyQuarter!=caEDraftDiscountQtyMonth){
            	StringBuffer sb=new StringBuffer();
            	sb.append(area);
            	sb.append("电子商业承兑汇票的贴现笔数与每月汇总不同");
            	errorTips.add(sb.toString());
            }
            if(caEDraftDiscountAmtQuarter!=caEDraftDiscountAmtMonth){
            	StringBuffer sb=new StringBuffer();
            	sb.append(area);
            	sb.append("电子商业承兑汇票的贴现金额与每月汇总不同");
            	errorTips.add(sb.toString());
            }
		
		}

		if (errorTips.size() != 0) {
			throw new Exception(errorTips.toString());
		}
	}

	private int calculateQty(Row row, int[] cellNums) {

		int result = 0;
		for (int cellNum : cellNums) {
			result += row.getCell(cellNum).getNumericCellValue();
		}
		return result;
	}

	private double calculateAmt(Row row, int[] cellNums) {

		BigDecimal result =new BigDecimal(0);
		for (int cellNum : cellNums) {
			double value=row.getCell(cellNum).getNumericCellValue();
			result=result.add(BigDecimal.valueOf(value).setScale(2, BigDecimal.ROUND_HALF_UP));
		}
		return result.doubleValue();
	}

	private String validateMeanSection(Row row, String area, int qtyNum, int amtQty, int section, String billType) {
		int qty = (int) row.getCell(qtyNum).getNumericCellValue();
		double amt = row.getCell(amtQty).getNumericCellValue();
		if (qty == 0) {
			return null;
		}
		double avg = amt / qty;
		switch (section) {
		case Constant.less50:

			if (avg >= 50) {
				StringBuffer sb = new StringBuffer();
				sb.append(area).append(billType).append("笔数：").append(qtyNum).append("金额：").append("笔均不在50万元以下");
				return sb.toString();
			}
			break;
		case Constant.bet50To100:
			if (avg < 50 || avg >= 100) {
				StringBuffer sb = new StringBuffer();
				sb.append(area).append(billType).append("笔数：").append(qtyNum).append("金额：").append("笔均不在50（含）至100");
				return sb.toString();
			}
			break;
		case Constant.bet100To300:
			if (avg < 100 || avg >= 300) {
				StringBuffer sb = new StringBuffer();
				sb.append(area).append(billType).append("笔数：").append(qtyNum).append("金额：").append("笔均不在100（含）至300");
				return sb.toString();
			}
			break;
		case Constant.gt300:
			if (avg < 300) {
				StringBuffer sb = new StringBuffer();
				sb.append(area).append(billType).append("笔数：").append(qtyNum).append("金额：").append("笔均不在300万元（含）以上");
				return sb.toString();
			}
			break;
		}
		return null;
	}

}
